package com.zr.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.zr.dao.ContentDao;
import com.zr.model.Content;
import com.zr.model.CurrentContent;
import com.zr.model.Type;
import com.zr.util.JDBCUtil;

/**
 * 对数据库表content表操作的具体实现类
 * @author lf
 *
 */
public class ContentDaoImpl implements ContentDao{

	@Override
	public ArrayList<CurrentContent> getAllContent() {
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCname(rs.getString("cname"));
				cc.setCstate(rs.getString("cstate"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCtitle(rs.getString("ctitle"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getAllCount() {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid)AS csum FROM e_content ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public ArrayList<CurrentContent> getContentByLimmit(int page, int pageSize) {
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		int start = (page-1)*pageSize;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public ArrayList<CurrentContent> getContentByAll(String title, String center, String startDate, String endDate,
			String state,int page,int pageSize) {
		ArrayList<CurrentContent> list = new ArrayList<>();
		int start = (page-1)*pageSize;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate like ? ");
		sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			pst.setInt(4, start);
			pst.setInt(5, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int getCountByAll(String title, String center, String startDate, String endDate, String state) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(e_content.cid) AS csum FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate like ? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public ArrayList<CurrentContent> getContentByCSES(String center, String startDate, String endDate,
			String state,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");
		sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			pst.setInt(4, start);
			pst.setInt(5, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getConuntByCSES(String center, String startDate, String endDate, String state) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public ArrayList<CurrentContent> getContentByTSES(String title, String startDate, String endDate, String state,int page,int pageSize) {
		ArrayList<CurrentContent> list = new ArrayList<>();
		int start = (page-1)*pageSize;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");
		sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			pst.setInt(4,start);
			pst.setInt(5, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCountByTSES(String title, String startDate, String endDate, String state) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");
		sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public ArrayList<CurrentContent> getContentByTCES(String title, String center, String endDate, String state,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN '1800-1-1' AND ? ");
		sql.append("AND cstate=? ");sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, endDate);
			pst.setString(2, state);
			pst.setInt(3,start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCountByTCES(String title, String center, String endDate, String state) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN '1800-1-1' AND ? ");
		sql.append("AND cstate=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, endDate);
			pst.setString(2, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	
	@Override
	public ArrayList<CurrentContent> getContentByTCSS(String title, String center, String startDate, String state,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String d = df.format(date);
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, d);
			pst.setString(3, state);
			pst.setInt(4,start);
			pst.setInt(5, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	
	@Override
	public int getCountByTCSS(String title, String center, String startDate, String state) {
		int total = -1;
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String d = df.format(date);
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, d);
			pst.setString(3, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	
	@Override
	public ArrayList<CurrentContent> getContentByTCSE(String title, String center, String startDate, String endDate,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setInt(3,start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	
	@Override
	public int getCountByTCSE(String title, String center, String startDate, String endDate) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		sql.append("AND ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		sql.append("AND ctime BETWEEN ? AND ? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	
	@Override
	public ArrayList<CurrentContent> getContentBySES(String startDate, String endDate, String state,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			pst.setInt(4,start);
			pst.setInt(5, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	
	@Override
	public int getCountBySES(String startDate, String endDate, String state) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ctime BETWEEN ? AND ? ");
		sql.append("AND cstate=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startDate);
			pst.setString(2, endDate);
			pst.setString(3, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public ArrayList<CurrentContent> getContentByCname(String cname, int page, int pageSize) {
		ArrayList<CurrentContent> list = new ArrayList<>();
		int size = (page-1)*pageSize;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content WHERE cname=? LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, cname);
			pst.setInt(2, size);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCname(rs.getString("cname"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCountByCname(String cname) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content WHERE cname=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, cname);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}
	
	@Override
	public Content getContentByCid(int cid) {
		// TODO Auto-generated method stub

		Content content = new Content();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM e_content WHERE e_content.cid=? ");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, cid);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCname(rs.getString("cname"));
				content.setCabstract(rs.getString("cabstract"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCcontent(rs.getString("ccontent"));
				content.setCspecial(rs.getString("cspecial"));
				content.setCnumber(rs.getInt("cnumber"));
				content.setCstate(rs.getString("cstate"));
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
		}
		return content;
	}

	@Override
	public ArrayList<CurrentContent> getContentByCtitle(String title,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setInt(1,start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCountByCtitle(String title) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ctitle LIKE '%");
		sql.append(title);
		sql.append("%' ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public ArrayList<CurrentContent> getContentByCenter(String center,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setInt(1,start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public ArrayList<CurrentContent> getContentByStartTime(String startTime,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String d = df.format(date);
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctime BETWEEN ? AND ? ");sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startTime);
			pst.setString(2, d);
			pst.setInt(3,start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCountByStartTime(String startTime) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String d = df.format(date);
		sql.append("SELECT COUNT(cid) AS csum  FROM e_content ");
		sql.append("WHERE ctime BETWEEN ? AND ? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, startTime);
			pst.setString(2, d);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;

	}

	@Override
	public ArrayList<CurrentContent> getContentByEndTime(String endTime,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String d = df.format(date);
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content ");
		sql.append("WHERE ctime BETWEEN ? AND ? ");sql.append("LIMIT ?,?");

		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, d);
			pst.setString(2, endTime);
			pst.setInt(3,start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCname(rs.getString("cname"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;

	}

	@Override
	public int getCountByEndTime(String endTime) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String d = df.format(date);
		sql.append("SELECT COUNT(cid) AS csum  FROM e_content ");
		sql.append("WHERE ctime BETWEEN ? AND ? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, d);
			pst.setString(2, endTime);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;

		
	}

	@Override
	public ArrayList<CurrentContent> getContentByState(String state,int page,int pageSize) {
		int start = (page-1)*pageSize;
		ArrayList<CurrentContent> list = new ArrayList<>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT cid,ctitle,cname,ctime,cstate FROM e_content WHERE cstate=? ");
		sql.append("LIMIT ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, state);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				CurrentContent cc = new CurrentContent();
				cc.setCid(rs.getInt("cid"));
				cc.setCname(rs.getString("cname"));
				cc.setCtitle(rs.getString("ctitle"));
				cc.setCtime(rs.getDate("ctime").toString());
				cc.setCstate(rs.getString("cstate"));
				cc.setOperation("<a onclick='javascript:update(this)' id='"+rs.getInt("cid")+"'>修改</a>&nbsp;&nbsp;&nbsp;&nbsp;"
						+ "&nbsp;<a onclick='javascript:delet(this)' id='"+rs.getInt("cid")+"'>删除</a>");
				list.add(cc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getCountByState(String state) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content WHERE cstate=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;

	}

	@Override
	public int getCountByCenter(String center) {
		int total = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cid) AS csum FROM e_content ");
		sql.append("WHERE ccontent LIKE '%");
		sql.append(center);
		sql.append("%' ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				total = rs.getInt("csum");
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}
	
	@Override
	public ArrayList<Content> getRelevantContentByCname(String cname,int cid) {
		ArrayList<Content> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM e_content ");
		sql.append("WHERE e_content.cname=? ");
		sql.append("AND cid <> ? ");
		sql.append("ORDER BY e_content.cnumber DESC ");
		sql.append("LIMIT 0,10 ");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setString(1, cname);
			ps.setInt(2, cid);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCname(rs.getString("cname"));
				content.setCabstract(rs.getString("cabstract"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCcontent(rs.getString("ccontent"));
				content.setCspecial(rs.getString("cspecial"));
				content.setCnumber(rs.getInt("cnumber"));
				content.setCstate(rs.getString("cstate"));
				list.add(content);
				
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
		}
		return list;
	}

	@Override
	public ArrayList<Type> getMaxNumberContent() {
		ArrayList<Type> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM e_type ");
		/*sql.append("ORDER BY e_content.cnumber DESC ");
		sql.append("LIMIT 0,5 ");*/
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				Type content = new Type();
				content.setTid(rs.getInt("tid"));
				content.setTname(rs.getString("tname"));
				list.add(content);
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public void addContent(String title, String abstract1, String type, String file, String special) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO e_content(e_content.ctitle,e_content.cabstract,e_content.cname,e_content.ccontent,e_content.cspecial,e_content.cstate,e_content.ctime,e_content.cnumber) ");
		sql.append("VALUES (?,?,?,?,?,'有效',?,0)");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setString(1, title);
			ps.setString(2, abstract1);
			ps.setString(3, type);
			ps.setString(4, file);
			ps.setString(5, special);
			Date date = new Date();
			DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			String ctime = df.format(date);
			ps.setString(6, ctime);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	@Override
	public int getCount(String cname) {

		int count = -1;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(cname) AS cnameSum FROM e_content WHERE cname=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1,cname);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("cnameSum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public void deleteContent(int[] cid) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE e_content SET e_content.cstate='无效' WHERE e_content.cid=? ");
		Connection conn = JDBCUtil.getConnection();
		try {
			conn.setAutoCommit(false);
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			for(int i=0;i<cid.length;i++)
			{
				ps.setInt(1, cid[i]);
				ps.execute();
			}
			conn.commit();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	@Override
	public boolean deleteContentByCid(int cid) {
		boolean b = false;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE e_content SET cstate='无效' WHERE cid=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			int i = pst.executeUpdate();
			if(i>0){
				b = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}

	@Override
	public boolean updateContentByCid(int cid) {
		boolean b = false;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE e_content SET cstate='有效' WHERE cid=? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			int i = pst.executeUpdate();
			if(i>0){
				b = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}


}
